import pymysql
from Config import Config

class UserModel:
    def __init__(self,id = None,user = None,pwd = None,states = None,switchs_to = None,start_time=None, create_time = None,create_by = None,describe=None):
        self.id = id
        self.user = user
        self.pwd = pwd
        self.states = states
        self.switchs_to = switchs_to
        self.start_time = start_time
        self.create_time = create_time
        self.create_by = create_by
        self.describe = describe

    def save(self):
        config = Config()
        db_config = config.get_config()
        connection = pymysql.connect(**db_config)
        try:
            with connection.cursor() as cursor:
                if self.id == None:
                    sql = "INSERT INTO users(user,pwd,states,switchs_to,create_time,create_by) VALUES(%s,%s,%s,%s,%s,%s)"
                    cursor.execute(sql,(self.user,self.pwd,self.states,self.switchs_to,self.create_time,self.create_by))
                else:
                    sql = "UPDATE users SET user=%s,pwd=%s,states=%s,switchs_to=%s,create_time=%s,create_by=%s WHERE id=%s"
                    cursor.execute(sql,(self.user,self.pwd,self.states,self.switchs_to,self.create_time,self.create_by,self.id))
            connection.commit()
        finally:
            connection.close()
    def get_by_user(self):
        config = Config()
        db_config = config.get_config()
        connection = pymysql.connect(**db_config)
        try:
            with connection.cursor() as cursor:
                sql = "SELECT * FROM users WHERE user=%s"
                cursor.execute(sql,(self.user,))
                result = cursor.fetchall()
                return result
        finally:
            connection.close()

    @staticmethod
    def update_by_user(target_state,target_switchs_to,target_user):
        config = Config()
        db_config = config.get_config()
        #根据user编号更改机器人工作状态
        connection = pymysql.connect(**db_config)
        try:
            with connection.cursor() as cursor:
                sql = "UPDATE users SET states=%s,switchs_to=%s WHERE user=%s"
                cursor.execute(sql,(target_state,target_switchs_to,target_user))
                connection.commit()
        finally:
            connection.close()


    def update_state_by_user(self):#通过机器人编号，更新机器人工作状态
        config = Config()
        db_config = config.get_config()
        connection = pymysql.connect(**db_config)
        try:
            with connection.cursor() as cursor:
                sql = "UPDATE users SET states=%s WHERE user=%s"
                cursor.execute(sql,(self.states,self.user))
                connection.commit()
        finally:
            connection.close()


    @staticmethod
    def get_by_states(target_state): #根据工作状态查询机器人信息
        config = Config()
        db_config = config.get_config()
        connection = pymysql.connect(**db_config)
        try:
            with connection.cursor() as cursor:
                sql = "SELECT a.*,b.describe FROM users as a left join boss.switched as b on a.switchs_to=b.switchs WHERE states=%s"
                cursor.execute(sql,target_state)
                result = cursor.fetchall()
                # print(result)
                return [UserModel(**row) for row in result]
        finally:
            connection.close()
    @staticmethod
    def get_qty_by_states(target_state):
        config = Config()
        db_config = config.get_config()
        connection = pymysql.connect(**db_config)
        try:
            with connection.cursor() as cursor:
                sql = "SELECT count(user) as qty FROM users WHERE states=%s"
                cursor.execute(sql,target_state)
                result = cursor.fetchone()
                return result
        finally:
            connection.close()


    @staticmethod
    def get_all():
        config = Config()
        db_config = config.get_config()
        connection = pymysql.connect(**db_config)
        try:
            with connection.cursor() as cursor:
                sql = "SELECT * FROM users"
                cursor.execute(sql)
                result = cursor.fetchall()
                return [UserModel(**row) for row in result]
        finally:
            connection.close()


if __name__ == '__main__':
    user = UserModel(user='183')
    user_data=user.get_by_user()
    if user_data:
        print(user_data)
        print(user_data[0]['pwd'])
    else:
        print('eeeeee')


